package com.itsitio.registeradmin.dao.impl;

import java.util.List;
import java.util.Map;

import org.hibernate.Criteria;
import org.hibernate.Hibernate;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Disjunction;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.Transformers;
import org.hibernate.type.Type;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.itsitio.registeradmin.dao.CompanyDao;
import com.itsitio.registeradmin.dao.CompanyFirmDao;
import com.itsitio.registeradmin.dao.base.SpringHibernateDao;
import com.itsitio.registeradmin.dto.CompanyDTO;
import com.itsitio.registeradmin.model.Company;
import com.itsitio.registeradmin.model.CompanyFirmName;

@Repository
public class CompanyFirmDaoImpl extends SpringHibernateDao<CompanyFirmName> implements CompanyFirmDao{

	@Autowired
	public CompanyFirmDaoImpl(SessionFactory sessionFactory) {
		super(sessionFactory);
	}

	@Override
	protected Class<CompanyFirmName> getObjetoManejado() {
		return CompanyFirmName.class;
	}

	public List<Company> getCompanyListByCriteria(String firmName,
			String taxId, String tradeName) {
		
		
		Criterion firmNameCrit = null;
		
		Criterion tradeNameCrit = null;
		
		Criterion taxIdCrit = null;
		
		Disjunction disjunction = Restrictions.disjunction();
		
		Criteria criteria = getSession().createCriteria(getObjetoManejado());
		
		
		if(firmName != null){
			firmNameCrit = Restrictions.like("firmName", firmName, MatchMode.ANYWHERE);
			disjunction.add(firmNameCrit);
		}
		
		if(tradeName != null){
			tradeNameCrit = Restrictions.like("tradeName", tradeName, MatchMode.ANYWHERE);
			disjunction.add(tradeNameCrit);
		}
		
		if(taxId != null){
			taxIdCrit = Restrictions.eq("taxId", taxId);
			disjunction.add(taxIdCrit);
		}
		
		criteria.add(disjunction);

	    
	    List<Company> results = criteria.list();
		
		return results;
	}

	/* (non-Javadoc)
	 * @see com.itsitio.registeradmin.dao.CompanyDao#getCompanyListByTaxId(java.lang.String)
	 */
	public List<Company> getCompanyListByTaxId(String taxId) {
		DetachedCriteria criteria = DetachedCriteria.forClass(getObjetoManejado());
		criteria.add(Restrictions.eq("taxId", taxId));
		return  getHibernateTemplate().findByCriteria(criteria);
	}

	@SuppressWarnings("unchecked")
	public List<CompanyDTO> getCompanyDtoListByCriteria(String taxId, String firmName) {
		
		String queryEmpresas = "select  e.idx as idEmpresa, null as idRazon, e.nombre as firmName,e.fantasia as tradeName," +
				"t.descripcion as type, e.website as website,e.cuit as taxId, 0 as entityType " +
				"from empresas e left outer join empresas_tipo t on e.tipo = t.idx " +
				"where " + (!taxId.isEmpty()? " e.cuit like :taxId ":"") + 
				(!taxId.isEmpty() && !firmName.isEmpty()? " or ": "")+
				(!firmName.isEmpty()? " e.nombre like :firmName ":"");
		
		String queryRazones = "select r.idxempresa as idEmpresa, r.idx as idRazon, r.razon as firmName, null as tradeName, " +
				"null as type, null as website, r.cuit as taxId, 1 as entityType " +
				"from empresas_razones r where " + (!taxId.isEmpty()? " r.cuit =:taxId ":"") + 
				(!taxId.isEmpty() && !firmName.isEmpty()? " or ": "")+
				(!firmName.isEmpty()? " r.razon like :firmName ":"");
		
		String union = "select emp.idEmpresa as id, emp.idRazon as aliasId, emp.firmname as firmName, emp.tradeName as tradeName," +
				" emp.type as type, emp.website as website, emp.taxId as taxId, emp.entityType as entityType " +
				" from(" + queryEmpresas + " union all " + queryRazones + ") emp order by  emp.idEmpresa, emp.entityType";
		
		SQLQuery query = (SQLQuery) getSession().createSQLQuery(union);
		
		if(!taxId.isEmpty()){
			query.setParameter("taxId", taxId);
		}
		if(!firmName.isEmpty()){
			query.setParameter("firmName", firmName.substring(0, 3) + '%');
		}
						
						
		List<CompanyDTO> companyList = query.addScalar("id")
				  .addScalar("aliasId")
				  .addScalar("firmName")
				  .addScalar("tradeName")
				  .addScalar("type")
				  .addScalar("website")
				  .addScalar("taxId")
				  .addScalar("entityType",Hibernate.INTEGER)
				  .setResultTransformer( Transformers.aliasToBean(CompanyDTO.class))
				  .list();
		
		return companyList;

	}

	
	@SuppressWarnings("unchecked")
	public List getCompanyDtoListByCriteria2(String taxId) {
	//		String sql = "select new Company(company.firmName, company.taxId, companyFirmNames) " +	
		String sql = "select new map(company, companyFirmNames) " +
				"from Company as company left join company.companyFirmNames as companyFirmNames with companyFirmNames.firmName like 'ITS%' " +
				"where company.firmName like 'ITS%' ";
		
		List companyList = getSession().createQuery(sql)
				  //.setResultTransformer( Transformers.aliasToBean(Company.class))
				  .list();
		
		return companyList;

	}
}
